package dbLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import dbLayer.DbConnection;
import modelLayer.Equipment;

/**
 * 
 * @author Slayer
 *
 * There might be some few errors. Must check before launching.
 */
public class DBEquipment implements IFDBEquipment {

	private Connection con;
	
	public DBEquipment()
	{
		con = DbConnection.getInstance().getDBcon();
	}
	
	@Override
	public ArrayList<Equipment> getAllEquipment(boolean retriveAssociation) {
		// TODO Auto-generated method stub
		return miscWhere("", retriveAssociation);
	}

	@Override
	public Equipment findEquipment(int id, boolean retriveAssociation) {
		// TODO Auto-generated method stub
		String wClause = "  id = '" + id + "'";
        return singleWhere(wClause, retriveAssociation);
	}

	@Override
	public Equipment searchEquipmentOnName(String attValue, boolean retriveAssociation) {
		// TODO Auto-generated method stub
		String wClause = "typeName like '%" + attValue + "%'";
        System.out.println("Search Equipment " + wClause);
        return singleWhere(wClause, retriveAssociation);
	}

	@Override
	public int insert(Equipment equipment) throws Exception {
		// TODO Auto-generated method stub
		int nextID = GetMax.getMaxId("Select max(id) from equipment");
		nextID = nextID + 1;
		
		System.out.println("Next ID : " + nextID);
		
		int rc = -1;
		
		String query = "INSERT INTO Equipment(id, typeName, description)  VALUES('"+
		  		nextID + "','"  +
			    equipment.getProduct().getID()  + "','"  +
	            equipment.getType() + "','" +
	            equipment.getDescription() +"')";
		
		System.out.println("insert : " + query);
		
		try
		{
			Statement stmt = con.createStatement();
	        stmt.setQueryTimeout(5);
	        rc = stmt.executeUpdate(query);
	        stmt.close();
		}
		catch (Exception e)
		{
			System.out.println("Equipment ikke oprettet");
	        throw new Exception ("Equipment is not inserted correct");
		}
		return rc;
	}

	@Override
	public int update(Equipment equip) {
		// TODO Auto-generated method stub
		Equipment equipment  = equip;
		int rc=-1;

		String query="UPDATE equipment SET " +
		 	  "id ='"+ equipment.getProduct().getID() +"', " +
		 	  "typeName ='"+ equipment.getType() + "', " +
              "description ='" + equipment.getDescription()
		 	  + "'";
		
		System.out.println("Update query:" + query);
  		
		try
  		{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in equipment db: " + ex);
	  	}
		return rc;
	}

	@Override
	public int delete(int id) {
		// TODO Auto-generated method stub
		int rc=-1;
		  
	  	String query="DELETE FROM equipment WHERE id = '" + id + "'";
	  	System.out.println(query);
	  	
	  	try
	  	{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}
   	    catch(Exception ex)
   	    {
	 	  	System.out.println("Delete exception in equipment db: "+ex);
	 	}
		return rc;
	}

	private ArrayList<Equipment> miscWhere(String wClause, boolean retrieveAssociation)
	{
        ResultSet results;
	    ArrayList<Equipment> list = new ArrayList<Equipment>();	
		
	    String query =  buildQuery(wClause);
  
        try{
        	Statement stmt = con.createStatement();
        	stmt.setQueryTimeout(5);
        	results = stmt.executeQuery(query);
	 	
	
        	while( results.next() ){
        		Equipment equipment = new Equipment();
        		equipment = buildEquipment(results);	
        		list.add(equipment);	
        	}
        	
            stmt.close();     
                
            if(retrieveAssociation)
            {
            	
            }
			
		}
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
        
		return list;
	}
	
	private String buildQuery(String wClause)
	{
	    String query="SELECT id, typeName, description  FROM Equipment";
		
		if (wClause.length()>0)
			query = query + " WHERE " + wClause;
			
		return query;
	}
	
	private Equipment buildEquipment(ResultSet results)
    {   
		Equipment equipment = new Equipment();
        try{
              equipment.getProduct().setID(results.getInt("id"));
              equipment.setType(results.getString("typeName"));
              equipment.setDescription(results.getString("description"));
        }
        catch(Exception e)
        {
           System.out.println("error in building the Equipment object");
        }
        return equipment;
    }
	
	private Equipment singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Equipment equipment = new Equipment();
           
		String query =  buildQuery(wClause);
		System.out.println(query);
		try
		{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
	 			equipment = buildEquipment(results);
                            
	 			stmt.close();
	 			if(retrieveAssociation)
	 			{
                               
	 			}
			}
	 		else{
	 			equipment = null;
	 		}
		}
		catch (Exception e)
		{
	 		System.out.println("Query exception: "+ e);
	 	}
		return equipment;
	}
}
